from sqlalchemy import Integer, and_, cast, func, insert, select, update

from forward.dependencies.database import db
from forward.model.orm.base import BaseORM
from forward.model.schemas.lucky.order import (
    ActivityOrderCreateSchema,
    ActivityOrderOutSchema,
)
from forward.model.tables.activity import ActivityOrderModel
from forward.utils.page_utils import PageSchema, PageUtils


class ActivityOrderORM(BaseORM[ActivityOrderOutSchema]):
    pass
    MODEL = ActivityOrderModel
    OUT_SCHEMA = ActivityOrderOutSchema

    async def create_(self, order_in: ActivityOrderCreateSchema):
        statement = insert(ActivityOrderModel).values(
            order_in.model_dump(mode="json"))
        id = await db.execute(statement)
        return id

    async def update_(self, id: int, order_in: ActivityOrderCreateSchema):

        statement = (
            update(ActivityOrderModel)
            .values(order_in.model_dump(mode="json"))
            .where(ActivityOrderModel.id == id)
        )
        count = await db.execute(statement)
        return await self.get_one_by_id(id)

    # async def get_one_by_id(self, id: int) -> ActivityOrderOutSchema | None:
    #     statement = select(ActivityOrderModel).where(
    #         ActivityOrderModel.id == id)
    #     record = await db.execute(statement)
    #     if not record:
    #         return None
    #     return ActivityOrderOutSchema.model_validate(record, from_attributes=True)

    async def get_one_by_params(self, **kwargs) -> ActivityOrderOutSchema | None:
        conditions = []
        for key, value in kwargs.items():
            if not hasattr(ActivityOrderModel, key):
                continue  # 跳过不存在的字段
            conditions.append(getattr(ActivityOrderModel, key) == value)
        statement = select(ActivityOrderModel).where(and_(*conditions))
        record = await db.fetch_one(statement)
        if not record:
            return None
        one = ActivityOrderOutSchema.model_validate(
            record, from_attributes=True)
        return one

    @classmethod
    async def get_all_by_user_id(
        cls, user_id: int, activity_id: int
    ) -> list[ActivityOrderOutSchema] | list[None]:
        stmt = (
            select(ActivityOrderModel)
            .where(
                cast(func.json_extract(ActivityOrderModel.user, "$.id"), Integer)
                == user_id
            )
            .where(
                cast(func.json_extract(ActivityOrderModel.activity, "$.id"), Integer)
                == activity_id
            )
        )
        records = await db.fetch_all(stmt)
        if not records:
            return []
        return [
            ActivityOrderOutSchema.model_validate(record, from_attributes=True)
            for record in records
        ]

    async def lists(
        self, page_num: int = 1, page_size: int = 20
    ) -> PageSchema[ActivityOrderOutSchema]:
        statement = (
            select(ActivityOrderModel)
            .where(ActivityOrderModel.is_delete == 0)
            .order_by(ActivityOrderModel.create_time.desc())
        )
        pagination = await PageUtils.paginate(
            db=db,
            query=statement,
            page_num=page_num,
            page_size=page_size,
            schema=ActivityOrderOutSchema,
        )
        return pagination

    async def get_count_by_activity_id(self, activity_id: int) -> int:
        statement = (
            select(func.count())
            .select_from(ActivityOrderModel)
            .where(ActivityOrderModel.activity_id == activity_id)
        )
        count = await db.fetch_val(statement)
        return count
